-------------------------------------------------------------------------------
-- Functions needed to support the Conversion
-------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetNumericDatabaseVersion]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetNumericDatabaseVersion]
GO

CREATE FUNCTION [dbo].[GetNumericDatabaseVersion]()
RETURNS int
AS
BEGIN
	declare @version VARCHAR(30)
	declare @numericVersion int

	select @version=VersionName
	from Version
	where Description = 'Insight'

	select @numericVersion = convert( int , 
	case when substring( @version , 1 , 1 ) LIKE '[0-9]' then substring( @version , 1 , 1 ) else '' end +
	case when substring( @version , 2 , 1 ) LIKE '[0-9]' then substring( @version , 2 , 1 ) else '' end +
	case when substring( @version , 3 , 1 ) LIKE '[0-9]' then substring( @version , 3 , 1 ) else '' end +
	case when substring( @version , 4 , 1 ) LIKE '[0-9]' then substring( @version , 4 , 1 ) else '' end +
	case when substring( @version , 5 , 1 ) LIKE '[0-9]' then substring( @version , 5 , 1 ) else '' end +
	case when substring( @version , 6 , 1 ) LIKE '[0-9]' then substring( @version , 6 , 1 ) else '' end +
	case when substring( @version , 7 , 1 ) LIKE '[0-9]' then substring( @version , 7 , 1 ) else '' end +
	case when substring( @version , 8 , 1 ) LIKE '[0-9]' then substring( @version , 8 , 1 ) else '' end +
	case when substring( @version , 9 , 1 ) LIKE '[0-9]' then substring( @version , 9 , 1 ) else '' end
	)

	return  @numericVersion
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FormatDatabaseVersion]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FormatDatabaseVersion]
GO

CREATE FUNCTION [dbo].[FormatDatabaseVersion]
(
	@numericVersion int
)
RETURNS VARCHAR(30)
AS
BEGIN

	declare @version varchar(30)
    set @version = cast(@numericVersion as varchar)
    set @version =
		SUBSTRING(@version, 1, 1) + '.' +
		SUBSTRING(@version, 2, 1) + '.' +
		SUBSTRING(@version, 3, 1) + '.' +
		SUBSTRING(@version, 4, 3)

	return @version
END
GO

-------------------------------------------------------------------------------
-- Setup the version for 2.3.0 release
-------------------------------------------------------------------------------
BEGIN

	IF ( [dbo].[GetNumericDatabaseVersion]() < 230000 )
	BEGIN

	declare @dbversion	VARCHAR(30)
	set @dbversion = null

		BEGIN
			set @dbversion = '2.3.0.000'
            update Version
			set VersionName = @dbversion
			where Description = 'Insight'

		    PRINT 'Insight database version now ' + @dbversion
		END

	END -- End of version setup

END
GO

-------------------------------------------------------------------------------
-- From here on, all the new changes will be added for 2.3.0 release
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- 2.3.0.001
-------------------------------------------------------------------------------
BEGIN
	IF ( [dbo].[GetNumericDatabaseVersion]() < 230001 )
	BEGIN


		-- CJR20 TrackIt 8844.  Remove direct access to protocols for protocol administrators, they should have it 
		-- through org.
		BEGIN
			DELETE FwkResource_DomainUser_Map
			FROM FwkResource_DomainUser_Map map 
			INNER JOIN
			(
			SELECT		DISTINCT (p.ProtocolAdminPersonId ), p.ProtocolNumber, p.FwkResourceID, du.Id, dp.IsActive, p.ModuleType
			FROM		Protocol p
			INNER JOIN	AdmPerson ap
				ON			p.ProtocolAdminPersonId = ap.Id
			INNER JOIN	FwkDomainUser du
				ON			ap.FwkDomainUserId = du.Id
			INNER JOIN	FwkDomainPrincipal dp
				ON			du.FwkPrincipalId = dp.Id
			LEFT JOIN	UserResourceCache UC
				ON  UC.ResourceId = p.FwkResourceId AND UC.FwkDomainUserId = Du.ID
			WHERE		p.IsCurrent = 1
					and P.ProtocolAdminPersonid is not NULL
					and UC.ResourceTypeId = 2
					and (UC.Id is null OR (64 & Uc.AccessLevel = 0 AND 128 & Uc.AccessLevel = 0 AND 256 & Uc.AccessLevel = 0) OR
					(262144 & Uc.AccessLevel = 0 AND 524288 & Uc.AccessLevel = 0 AND 1048576 & Uc.AccessLevel = 0))

			UNION 

			SELECT		DISTINCT (p.ProtocolAdminPersonId ), p.ProtocolNumber, p.FwkResourceID, du.Id, dp.IsActive, p.ModuleType
			FROM		Protocol p
			INNER JOIN	AdmPerson ap
				ON			p.ProtocolAdminPersonId = ap.Id
			INNER JOIN	FwkDomainUser du
				ON			ap.FwkDomainUserId = du.Id
			INNER JOIN	FwkDomainPrincipal dp
				ON			du.FwkPrincipalId = dp.Id
			WHERE		p.ProtocolNumber is NULL
					and P.MarkForDeletion = 0
					and P.ProtocolAdminPersonid is not NULL
		
			--UNION all the pending protocols so that Dan & Ros can see them....
			UNION 
		
			SELECT		ap.Id, p.ProtocolNumber, p.FwkResourceID, du.Id, dp.IsActive, p.ModuleType
			FROM		Protocol p, admPerson ap		
			INNER JOIN	FwkDomainUser du
				ON			ap.FwkDomainUserId = du.Id
			INNER JOIN	FwkDomainPrincipal dp
				ON			du.FwkPrincipalId = dp.Id
			WHERE		p.ProtocolNumber is NULL
					and P.MarkForDeletion = 0
					and P.ProtocolAdminPersonid is not NULL
					and ap.Id in (select PersonId from Organization_WorkflowRole_Person_Map 
							where WorkflowRole in ('HROAdminPerson', 'SRACIntake')) 
			) a on a.fwkresourceid = map.fwkresourceid and a.Id = map.fwkdomainuserid

			if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateProtocolAdminSecurity]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
			drop procedure [dbo].[UpdateProtocolAdminSecurity]
		END		

		-- TrackIt 9534
		BEGIN
			update refcodeattribute 
			set attribute = 'ANIMALSORDER'
			from refcodeattribute rca
			inner join (select refcode from refcodeattribute where attribute='ANIMALS' AND refdomain='ATTACHMENTTYPE') a on a.refcode = rca.refcode
			WHERE attribute = 'ORDER'

			update refcodeattribute 
			set attribute = 'HUMANSORDER'
			from refcodeattribute rca
			inner join (select refcode from refcodeattribute where attribute='HUMANS' AND refdomain='ATTACHMENTTYPE') a on a.refcode = rca.refcode
			WHERE attribute = 'ORDER'

			UPDATE refcodeattribute SET VALUE = '03'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_ADVRTSMNT'

			UPDATE refcodeattribute SET VALUE = '06'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_ASSNTFRM'

			UPDATE refcodeattribute SET VALUE = '09'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_CNSNTFRM'

			UPDATE refcodeattribute SET VALUE = '12'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_DTCOLLFRM'

			INSERT INTO refcodeattribute (refdomain, refcode, attribute, [value])
			VALUES('ATTACHMENTTYPE', 'ATCHTYPE_DETPROT', 'HUMANSORDER', '15')

			UPDATE refcodeattribute SET VALUE = '18'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_DEVBRCHR'

			UPDATE refcodeattribute SET VALUE = '21'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_DIARIES'

			UPDATE refcodeattribute SET VALUE = '24'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_DRGBRCHR'

			UPDATE refcodeattribute SET VALUE = '27'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_DSMBDMCReport'

			UPDATE refcodeattribute SET VALUE = '30'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_ENRLREPORT'

			UPDATE refcodeattribute SET VALUE = '33'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_FLYER'

			UPDATE refcodeattribute SET VALUE = '36'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_NIHGRANT'

			UPDATE refcodeattribute SET VALUE = '39'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_IDEINFO'

			UPDATE refcodeattribute SET VALUE = '42'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_INDINFO'

			UPDATE refcodeattribute SET VALUE = '45'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_INSTRMNTQUSTNR'

			UPDATE refcodeattribute SET VALUE = '48'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_LSRTRNING'

			UPDATE refcodeattribute SET VALUE = '51'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_NSRINFO'

			INSERT INTO refcodeattribute (refdomain, refcode, attribute, [value])
			VALUES('ATTACHMENTTYPE', 'ATCHTYPE_OTHER', 'HUMANSORDER', '54')

			UPDATE refcodeattribute SET VALUE = '57'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_OTHGRANT'

			UPDATE refcodeattribute SET VALUE = '60'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_PCKGINSRT'

			UPDATE refcodeattribute SET VALUE = '63'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_POSTER'

			INSERT INTO refcodeattribute (refdomain, refcode, attribute, [value])
			VALUES('ATTACHMENTTYPE', 'ATCHTYPE_PROTOCOLSUMMARY', 'HUMANSORDER', '66')

			UPDATE refcodeattribute SET VALUE = '69'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_RCRTMNTLTR'

			UPDATE refcodeattribute SET VALUE = '72'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_RSPNSETORVW'

			UPDATE refcodeattribute SET VALUE = '75'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_SCHEMA'

			UPDATE refcodeattribute SET VALUE = '78'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_SPNSRAMNDMNT'

			INSERT INTO refcodeattribute (refdomain, refcode, attribute, [value])
			VALUES('ATTACHMENTTYPE', 'ATCHTYPE_STFCERTFRM', 'HUMANSORDER', '81')

			UPDATE refcodeattribute SET VALUE = '84'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_TLPHNSCRPT'
		END

		BEGIN
			declare @dbversion varchar(30)
			set @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
            update Version
			set VersionName = @dbversion
			where Description = 'Insight'

		    PRINT 'Insight database version now ' + @dbversion
		END

	END

END -- End 2.3.0.001

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- 2.3.0.002
-------------------------------------------------------------------------------
BEGIN
	IF ( [dbo].[GetNumericDatabaseVersion]() < 230002 )
	BEGIN

		-- Reporting fix to join PI on Unique ID 
		BEGIN
		if not exists(select 1 from INFORMATION_SCHEMA.columns where table_name = 'agragreement' and column_name = 'PIUniqueId') begin
				alter table dbo.agragreement add PIUniqueId varchar(20) null;
				create index IX_AgrAgreement_PropNumber on dbo.AgrAgreement (PropNumber)
				create index IX_Protocol_SvcEntityId on dbo.Protocol (SvcEntityId)
		end
		if not exists(select 1 from INFORMATION_SCHEMA.columns where table_name = 'agragreement_info' and column_name = 'PIUniqueId') begin
				exec('alter table agragreement_info add PIUniqueId varchar(20) null');
				create index IX_AgrAgreement_Info_PIUniqueId on dbo.agragreement_info (PIUniqueId)
				create index IX_PersonFacultyMap_AdmPersonId on dbo.AdmPerson_InfoEdFaculty_Map (AdmPersonId)
				create index IX_PersonFacultyMap_Unique_Id on dbo.AdmPerson_InfoEdFaculty_Map (Unique_Id)
		end
		END
		BEGIN
			--declare @dbversion varchar(30)
			set @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
            update Version
			set VersionName = @dbversion
			where Description = 'Insight'

		    PRINT 'Insight database version now ' + @dbversion
		END

	END

END -- End 2.3.0.002
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 2.3.0.003
-------------------------------------------------------------------------------
BEGIN
	IF ( [dbo].[GetNumericDatabaseVersion]() < 230003 )
	BEGIN

		-- Trackit #8792 Pro Fees changes. 
		BEGIN
		if not exists(select 1 from INFORMATION_SCHEMA.columns where table_name = 'Stg_mgh_cdmFeed' and column_name = 'TechProCode') begin
				exec('alter table Stg_mgh_cdmFeed add TechProCode nchar(1) null, ChargeTypeCode nchar(1) null');
		end
		if not exists(select 1 from INFORMATION_SCHEMA.columns where table_name = 'Mgh_cdmFeed' and column_name = 'TechProCode') begin
				exec('alter table Mgh_cdmFeed add TechProCode nchar(1) null, ChargeTypeCode nchar(1) null');
		end
		if not exists(select 1 from INFORMATION_SCHEMA.columns where table_name = 'ChargeMaster') begin
				exec('CREATE TABLE [dbo].[Chargemaster](
						[ID] int identity not null,
						[CPTCode] [varchar](20) NULL,
						[Description] [varchar](255) NULL,
						[NFprice] [money] NULL,
						[Price] [money] NULL,
						[Comments] [varchar](255) NULL,
						 CONSTRAINT [PK_Chargemaster] PRIMARY KEY CLUSTERED 
						(
							[ID] ASC
						)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
					) ON [PRIMARY]
					');
				/****** Object:  Index [IDX_ChargeMaster_CPTCode]    Script Date: 05/06/2009 15:09:40 ******/
				CREATE NONCLUSTERED INDEX [IDX_ChargeMaster_CPTCode] ON [dbo].[Chargemaster] ([CPTCode] ASC) ON [PRIMARY]

				/****** Object:  Index [IDX_ChargeMaster_Description]    Script Date: 05/06/2009 15:10:29 ******/
				CREATE NONCLUSTERED INDEX [IDX_ChargeMaster_Description] ON [dbo].[Chargemaster] ([Description] ASC) ON [PRIMARY]
		end
		END

		BEGIN
			--declare @dbversion varchar(30)
			set @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
            update Version
			set VersionName = @dbversion
			where Description = 'Insight'

		    PRINT 'Insight database version now ' + @dbversion
		END

	END

END -- End 2.3.0.003
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 2.3.0.004
-------------------------------------------------------------------------------
BEGIN
	IF ( [dbo].[GetNumericDatabaseVersion]() < 230004 )
	BEGIN

		-- Trackit #9227 Revenue Account numbers on Budget Detail, Budget Overview. 
		BEGIN
			if (select min(sortorder) from dbo.fincategory where sortorder is not null) < 10 begin 
				begin transaction Trackit_9227
				update fc set sortorder = fc.sortorder * 10 from dbo.fincategory fc
				update dbo.fincategory set sortorder = 142 where id = 15
				update dbo.fincategory set sortorder = 144 where id = 11
				update dbo.fincategory set sortorder = 146 where id = 21
				commit transaction Trackit_9227
			end
		END

		BEGIN
			--declare @dbversion varchar(30)
			set @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
            update Version
			set VersionName = @dbversion
			where Description = 'Insight'

		    PRINT 'Insight database version now ' + @dbversion
		END

	END

END -- End 2.3.0.004
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- 2.3.0.005
-------------------------------------------------------------------------------
BEGIN
	IF ( [dbo].[GetNumericDatabaseVersion]() < 230005 )
	BEGIN

		-- TrackIt issue 9735 - to clean up duplicate PIs for animal study staff. (Irina)
		BEGIN
				Update Map 
				set IsCurrent = 1
				from ProtocolStaff_Map map
				join Protocol p
					on map.ProtocolNumber = p.ProtocolNumber
					and p.IsCurrent = 1
				where map.ModuleType = 'A' and p.ModuleType = 'A'
				and map.ProtocoLStaffTypeId = 13
				and map.AdmPersonId = p.PIPersonID
				and map.IsCurrent = 0
				and AddedProtocolProcessID is not null

				select staff.ProtocolNumber, staff.AdmPersonId, ProtocolStaffTypeId, OrganizationId, count(*) as Number	--, staff.IsCurrent, p.IsCurrent
				into #temp1
				from ProtocolStaff_Map staff
				JOIN Protocol P 
				on staff.ProtocolNumber = p.ProtocolNumber
				where  staff.IsCurrent = 1 
				and p.IsCurrent = 1
				and  ProtocolStaffTypeId = 13
				and p.ModuleType = 'A'
				group by staff.ProtocolNumber, staff.AdmPersonId, ProtocolStaffTypeId, OrganizationId
				HAVING count(*) >1
				order by staff.ProtocolNumber, staff.AdmPersonId

				DELETE MAP from ProtocolStaff_Map map
				join #temp1 t
					on map.ProtocolNumber = t.ProtocolNumber
					and map.AdmPersonId = t.AdmPersonId
					and isnull(map.ProtocolStaffTypeId, 0) = isnull(t.ProtocolStaffTypeId, 0)
				where map.IsCurrent = 1	
				and map.id != (select min		(p1.id)
						FROM ProtocolStaff_Map p1
						WHERE map.ProtocolNumber = p1.ProtocolNumber
						and p1.AdmPersonId = map.AdmPersonId
						and isnull(P1.ProtocolStaffTypeId, 0) = isnull(map.ProtocolStaffTypeId, 0)
						and p1.IsCurrent = 1	)
						and map.ProtocolStaffTypeId = 13

				--Now delete not-current duplicates
				--select map.* from ProtocolStaff_Map map
					DELETE MAP from ProtocolStaff_Map map
					join Protocol p
						on map.ProtocolNumber = p.ProtocolNumber
						and p.IsCurrent = 1
					where map.ModuleType = 'A' and p.ModuleType = 'A'
					and map.ProtocoLStaffTypeId = 13
					and map.AdmPersonId = p.PIPersonID
					and map.IsCurrent = 0
					and AddedProtocolProcessID is  null
					and RemovedProtocolProcessID is null
				--(8488 row(s) affected)

				drop table #temp1
		END

		BEGIN
			--declare @dbversion varchar(30)
			set @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
            update Version
			set VersionName = @dbversion
			where Description = 'Insight'

		    PRINT 'Insight database version now ' + @dbversion
		END

	END

END -- End 2.3.0.005
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- 2.3.0.006
-------------------------------------------------------------------------------
BEGIN
	IF ( [dbo].[GetNumericDatabaseVersion]() < 230006 )
	BEGIN
		-- trackit 9359 & 9534 cjr20
		BEGIN
			UPDATE refcodeattribute SET VALUE = '03'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_PROTOCOLSUMMARY'

			UPDATE refcodeattribute SET VALUE = '06'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_SCHEMA'

			UPDATE refcodeattribute SET VALUE = '09'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_DETPROT'

			UPDATE refcodeattribute SET VALUE = '12'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_CNSNTFRM'

			UPDATE refcodeattribute SET VALUE = '15'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_ASSNTFRM'

			UPDATE refcodeattribute SET VALUE = '18'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_NIHGRANT'

			UPDATE refcodeattribute SET VALUE = '21'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_OTHGRANT'

			UPDATE refcodeattribute SET VALUE = '24'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_ADVRTSMNT'

			UPDATE refcodeattribute SET VALUE = '27'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_POSTER'

			UPDATE refcodeattribute SET VALUE = '30'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_FLYER'

			UPDATE refcodeattribute SET VALUE = '33'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_RCRTMNTLTR'

			UPDATE refcodeattribute SET VALUE = '36'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_TLPHNSCRPT'

			UPDATE refcodeattribute SET VALUE = '39'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_INSTRMNTQUSTNR'

			UPDATE refcodeattribute SET VALUE = '42'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_DIARIES'

			UPDATE refcodeattribute SET VALUE = '45'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_DRGBRCHR'

			UPDATE refcodeattribute SET VALUE = '48'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_PCKGINSRT'

			UPDATE refcodeattribute SET VALUE = '51'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_INDINFO'

			UPDATE refcodeattribute SET VALUE = '54'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_DEVBRCHR'

			UPDATE refcodeattribute SET VALUE = '57'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_NSRINFO'

			UPDATE refcodeattribute SET VALUE = '60'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_IDEINFO'

			UPDATE refcodeattribute SET VALUE = '63'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_DTCOLLFRM'

			UPDATE refcodeattribute SET VALUE = '66'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_SPNSRAMNDMNT'

			UPDATE refcodeattribute SET VALUE = '69'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_RSPNSETORVW'

			UPDATE refcodeattribute SET VALUE = '72'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_ENRLREPORT'

			UPDATE refcodeattribute SET VALUE = '75'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_DSMBDMCReport'

			UPDATE refcodeattribute SET VALUE = '78'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_LSRTRNING'

			UPDATE refcodeattribute SET VALUE = '81'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_OTHER'

			UPDATE refcodeattribute SET VALUE = '84'
			WHERE attribute = 'HumansOrder' AND refcode = 'ATCHTYPE_STFCERTFRM'
		END

		-- TrackIt 9737 cjr20
		BEGIN
			-- Remove an Extra degrees from person's degree list
			DELETE FROM AdmDegree_AdmPerson WHERE id in 
				(SELECT MAX(id)
				FROM admdegree_admperson 
				GROUP BY admpersonid, admdegreeid
				HAVING COUNT(*) > 1)
		END

		BEGIN
			--declare @dbversion varchar(30)
			set @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
            update Version
			set VersionName = @dbversion
			where Description = 'Insight'

		    PRINT 'Insight database version now ' + @dbversion
		END

	END

END -- End 2.3.0.006
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- 2.3.0.007
-------------------------------------------------------------------------------
BEGIN
	IF ( [dbo].[GetNumericDatabaseVersion]() < 230007 )
	BEGIN
		-- trackit 9737 cjr20
		-- convert PHD degrees to Ph.D and remove PHD
		BEGIN
			UPDATE AdmDegree_AdmPerson SET admdegreeid = 3147005 where admdegreeid = 77
			DELETE FROM AdmDegree WHERE Id = 77
		END

		BEGIN
			--declare @dbversion varchar(30)
			set @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
            update Version
			set VersionName = @dbversion
			where Description = 'Insight'

		    PRINT 'Insight database version now ' + @dbversion
		END

	END

END -- End 2.3.0.007
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- 2.3.0.008
-------------------------------------------------------------------------------
BEGIN
	IF ( [dbo].[GetNumericDatabaseVersion]() < 230008 )
	BEGIN
		-- trackit 8844 cjr20
		-- remove some old direct access permisions to protocols
		BEGIN
			delete fwkresource_domainuser_map
			from fwkresource_domainuser_map map
			inner join fwkdomainuser fdu on fdu.id = map.fwkdomainuserid
			inner join fwkresource r on r.id = map.fwkresourceid
			where fdu.username in ('lmh3', 'dmm25', 'rag5', 'dgc5', 'hl017')
			and r.fwkresourcetypeid = 2
		END

		BEGIN
			--declare @dbversion varchar(30)
			set @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
            update Version
			set VersionName = @dbversion
			where Description = 'Insight'

		    PRINT 'Insight database version now ' + @dbversion
		END

	END

END -- End 2.3.0.008
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- 2.3.0.009
-------------------------------------------------------------------------------
BEGIN
	IF ( [dbo].[GetNumericDatabaseVersion]() < 230009 )
	BEGIN
		-- trackit 9277 DM853
		-- remove some old direct access permisions to protocols
		BEGIN

			IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].RunEnvironment') AND type in (N'U'))
			DROP TABLE [dbo].RunEnvironment
			
			CREATE TABLE [dbo].RunEnvironment(
				[Id] [int] IDENTITY(1,1) NOT NULL,
				[RunEnvironmentName] varchar(256) NULL,
				[RunEnvironmentAddress] varchar(256) NULL
				)
	
		END

		BEGIN
			insert into RunEnvironment 
			values (' - QA', 'stage-insight.partners.org')
		END
		
		-- CJR20 add Order column to AdmDegree_AdmPerson 
		if not exists(select 1 from INFORMATION_SCHEMA.columns where table_name = 'AdmDegree_AdmPerson' and column_name = 'Order') 
		BEGIN
			ALTER TABLE AdmDegree_AdmPerson
			ADD [Order] INT NOT NULL CONSTRAINT [DF_AdmDegree_Order]  DEFAULT ((0))

			ALTER TABLE AdmDegree_AdmPerson DROP Constraint DF_AdmDegree_Order
		END

		BEGIN
			--declare @dbversion varchar(30)
			set @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
            update Version
			set VersionName = @dbversion
			where Description = 'Insight'

		    PRINT 'Insight database version now ' + @dbversion
		END

	END

END -- End 2.3.0.009
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 2.3.0.010
-------------------------------------------------------------------------------
BEGIN
	IF ( [dbo].[GetNumericDatabaseVersion]() < 230010 )
	BEGIN
		-- trackit 8792 KDH3 
		-- Add new pricing table and indexes for budget builder procedure picker.

		BEGIN			
			if NOT Exists(Select 1 from sys.objects WHERE object_id = OBJECT_ID(N'[dbo].ProcedurePickerPrices') AND type in (N'U'))
			begin
				exec('Create Table dbo.[ProcedurePickerPrices](
						Institution			varchar(10)
						,Dept				varchar(50) null
						,Description		varchar(255) null
						,Price				money null
						,ChargeCode			int null
						,CPTCode			varchar(50) null
						,ResearchRate		decimal(14,10) null
						,GovernmentRate		decimal(14,10) null
						,IndustryRate		decimal(14,10) null
						,ResearchCharge		money null
						,GovernmentCharge	money null
						,IndustryCharge		money null
						,GlobalFee			money null
						,NFGlobalFee		money null
						,ProFee				money null
						,POTechFee			money null
						,NFPOTechFee		money null
						,POProFee			money null
						,NFPOProFee			money null
						,Location			varchar(50) null
					)')
				Create Index IDX_ProcedurePickerPrices_ChargeCode on dbo.ProcedurePickerPrices(ChargeCode);
				Create Index IDX_ProcedurePickerPrices_Description on dbo.ProcedurePickerPrices(Description);
				Create Index IDX_ProcedurePickerPrices_CPTCode on dbo.ProcedurePickerPrices(CPTCode,Dept);
				Create Index IDX_ProcedurePickerPrices_Location on dbo.ProcedurePickerPrices(Location);
			end	
		
			if not exists(select 1 from INFORMATION_SCHEMA.columns where table_name = 'ClinicalTrialBudgetProcedure' and column_name = 'IsFacility') begin
				exec('alter table [dbo].[ClinicalTrialBudgetProcedure] add [IsFacility] [bit] NULL, [UseHospitalPricing] [bit] NULL');
			end
		END

		BEGIN
			--declare @dbversion varchar(30)
			set @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
            update Version
			set VersionName = @dbversion
			where Description = 'Insight'

		    PRINT 'Insight database version now ' + @dbversion
		END

	END

END -- End 2.3.0.010
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 2.3.0.011
-------------------------------------------------------------------------------
BEGIN
	IF ( [dbo].[GetNumericDatabaseVersion]() < 230011 )
	BEGIN
		-- trackit 9586 KDH3 
		-- Retro-salary change encumbrances.

		BEGIN			
			if NOT Exists(Select 1 from sys.objects WHERE object_id = OBJECT_ID(N'[dbo].PerSnapshotSalaryAdjustment') AND type in (N'U'))
			begin
				exec('CREATE TABLE [dbo].[PerSnapshotSalaryAdjustment](
					[Id] [int] NULL,
					[PerEmployeeJobMapId] [int] NOT NULL,
					[AnnualRate] [money] NULL,
					[EffectiveDate] [datetime] NULL,
					[ModifiedBy] [varchar](12) NULL,
					[ModifiedDate] [datetime] NOT NULL,
					[DateCreated] [datetime] NOT NULL,
					[SnapshotDate] [datetime]  NULL
				) ON [PRIMARY]')
				Create Index IX_PerSnapshotSalaryAdjustment_Id on [dbo].[PerSnapshotSalaryAdjustment](Id)
				Create Index IX_PerSnapshotSalaryAdjustment_JobMapId on [dbo].[PerSnapshotSalaryAdjustment](PerEmployeeJobMapId)
			end	

			if NOT Exists(Select 1 from sys.objects WHERE object_id = OBJECT_ID(N'[dbo].PerSnapshotSalaryDistribution') AND type in (N'U'))
			begin
				exec('CREATE TABLE [dbo].[PerSnapshotSalaryDistribution](
					[Id] [int] NULL,
					[AgrFundId] [int] NULL,
					[IsExternalFund] [bit] NOT NULL,
					[PerEmployeeJobMapId] [int] NOT NULL,
					[Description] [varchar](512) NULL,
					[EmployeeId] [varchar](12) NOT NULL,
					[HoursPerWeek] [int] NULL,
					[AnnualRate] [money] NULL,
					[PercentEffort] [decimal](10, 4) NULL,
					[EffectiveDate] [datetime] NULL,
					[ExpirationDate] [datetime] NULL,
					[Comment] [varchar](255) NULL,
					[ModifiedBy] [varchar](12) NULL,
					[DateLastModified] [datetime] NULL,
					[EarnPercent] [decimal](10, 3) NULL,
					[SnapshotDate] [datetime]  NULL
				) ON [PRIMARY]
				')
				Create Index IX_PerSnapshotSalaryDistribution_Id on [dbo].[PerSnapshotSalaryDistribution](Id)
				Create Index IX_PerSnapshotSalaryDistribution_JobMapId on [dbo].[PerSnapshotSalaryDistribution](PerEmployeeJobMapId)
			end	

			if NOT Exists(Select 1 from sys.objects WHERE object_id = OBJECT_ID(N'[dbo].PerSnapshotSalaryDistributionSplit') AND type in (N'U'))
			begin
				exec('CREATE TABLE [dbo].[PerSnapshotSalaryDistributionSplit](
					[Id] [int] NULL,
					[PerSalaryDistributionId] [int] NOT NULL,
					[PerSalaryAdjustmentId] [int] NOT NULL,
					[PercentEffort] [decimal](10, 4) NULL,
					[AnnualRate] [money] NULL,
					[EffectiveDate] [datetime] NULL,
					[ExpirationDate] [datetime] NULL,
					[ToBePaid]  AS (round(([AnnualRate] * datediff(day,[EffectiveDate],([ExpirationDate] + 1)) / 365),2)),
					[SnapshotDate] [datetime]  NULL
				) ON [PRIMARY]
				')
				Create Index IX_PerSnapshotSalaryDistribSplit_Id on [dbo].[PerSnapshotSalaryDistributionSplit](Id)
				Create Index IX_PerSnapshotSalaryDistribSplit_DistribId on [dbo].[PerSnapshotSalaryDistributionSplit](PerSalaryDistributionId)
				Create Index IX_PerSnapshotSalaryDistribSplit_AdjustId on [dbo].[PerSnapshotSalaryDistributionSplit](PerSalaryAdjustmentId)
			end	

			if NOT Exists(Select 1 from sys.objects WHERE object_id = OBJECT_ID(N'[dbo].PerRetroSalaryChangeEncumbrance') AND type in (N'U'))
			begin
				exec('CREATE TABLE [dbo].[PerRetroSalaryChangeEncumbrance](
					[Id] [int] IDENTITY(1,1) NOT NULL,
					[EncumbranceDate] [datetime] NULL,	
					[LastName] [varchar](64) NULL,
					[FirstName] [varchar](64) NULL,
					[FundNumber] [varchar](30) NULL,
					[AnnualRate] [money] NULL,
					[StartDate] [datetime] NULL,
					[EndDate] [datetime] NULL,
					[PerEmployeeJobMapId] [int] NULL,
					[MonthlyRate] [money] NULL,
					[MonthsRemaining] [decimal](16, 11) NULL,
					[CommittedSalary] [decimal](18, 11) NULL,
					[FringeRate] [decimal](10, 2) NULL,
					[CommittedFringe] [decimal](18, 8) NULL,
					[TotalCommitted] [decimal](18, 8) NULL,
					[ExpenseAmount] [money] NULL,
					[PercentEffort] [decimal](10, 4) NULL,
					[PercentageEffort] [decimal](18, 2) NULL,
					[FundId] [int] NULL,
					[EmployeeNumber] [varchar](12) NULL,
					[ProjectEndDate] [datetime] NULL,
					[SalaryAccountId] [int] NULL,
					[FringeAccountId] [int] NULL
				) ON [PRIMARY]')
				Create Index IX_PerRetroSalaryChangeEncumbrance_Id on [dbo].[PerRetroSalaryChangeEncumbrance](Id)
				Create Index IX_PerRetroSalaryChangeEncumbrance_LastName on [dbo].[PerRetroSalaryChangeEncumbrance](LastName)
				Create Index IX_PerRetroSalaryChangeEncumbrance_FirstName on [dbo].[PerRetroSalaryChangeEncumbrance](FirstName)
				Create Index IX_PerRetroSalaryChangeEncumbrance_EmployeeNumber on [dbo].[PerRetroSalaryChangeEncumbrance](EmployeeNumber)
				Create Index IX_PerRetroSalaryChangeEncumbrance_EncumbranceDate on [dbo].[PerRetroSalaryChangeEncumbrance](EncumbranceDate)
			end			

		END

		BEGIN
			--declare @dbversion varchar(30)
			set @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
            update Version
			set VersionName = @dbversion
			where Description = 'Insight'

		    PRINT 'Insight database version now ' + @dbversion
		END

	END

END -- End 2.3.0.011
-------------------------------------------------------------------------------

-- 2.3.0.012
-------------------------------------------------------------------------------
BEGIN
	IF ( [dbo].[GetNumericDatabaseVersion]() < 230012 )
	BEGIN
		-- DM changes 
		
		IF NOT EXISTS(select 1 from INFORMATION_SCHEMA.columns where table_name = 'Document' and column_name = 'UploadedToSharepoint')
		BEGIN
				EXEC('alter table Document add UploadedToSharepoint bit null');
		END
		IF NOT EXISTS(select 1 from INFORMATION_SCHEMA.columns where table_name = 'Document' and column_name = 'IsConvertedToPDF') 
		BEGIN
				EXEC('alter table Document add IsConvertedToPDF bit null'); 
		END

		--TrackIt 9703 changes START
		IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.columns 
			WHERE table_name = 'AgrAgreement_Fct' AND column_name = 'IsNoSubContractExpenseButBudget') 
		BEGIN
				EXEC('ALTER TABLE [dbo].[AgrAgreement_Fct] ADD [IsNoSubContractExpenseButBudget] [bit] NULL'); 
		END
		
		IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.columns 
			WHERE table_name = 'AgrAgreement_Fct' AND column_name = 'ISNoSubContractBudgetButExpense') 
		BEGIN
				EXEC('ALTER TABLE [dbo].[AgrAgreement_Fct] ADD [ISNoSubContractBudgetButExpense] [bit] NULL'); 
		END
		--TrackIt 9703 changes END

		BEGIN
			--declare @dbversion varchar(30)
			SET @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
			UPDATE Version
			SET VersionName = @dbversion
			WHERE Description = 'Insight'

			PRINT 'Insight database version now ' + @dbversion
		END
	END
	

END -- End 2.3.0.012
-------------------------------------------------------------------------------

-- 2.3.0.013
-------------------------------------------------------------------------------
BEGIN
	IF ( [dbo].[GetNumericDatabaseVersion]() < 230013 )
	BEGIN		
		
		--TrackIt 9703 changes START
		delete from Organization_Signoff_Map where Id = 57

	INSERT INTO FwkDomainOrganization 
	VALUES('Nursing','Nursing', 578, NULL, 1700, 'Partners > IHP > Nursing',3,1,'IHP',NULL,NULL,1,578,NULL,NULL,NULL,13863)

	update FwkDomainOrganization set DepartmentId = @@IDENTITY where Id= @@IDENTITY

	insert into Organization_Signoff_Map 
	values (@@IDENTITY, 27598, 0, 9320)

	INSERT INTO FwkDomainOrganization 
	VALUES('Physical Therapy','Physical Therapy', 578, NULL, 1700, 'Partners > IHP > Physical Therapy',3,1,'IHP',NULL,NULL,1,578,NULL,NULL,NULL,13863)

	update FwkDomainOrganization set DepartmentId = @@IDENTITY where Id= @@IDENTITY

	insert into Organization_Signoff_Map 
	values (@@IDENTITY, 25724, 0, 9320)

	INSERT INTO FwkDomainOrganization 
	VALUES('Comm. Science','Comm. Science', 578, NULL, 1700, 'Partners > IHP > Comm. Science',3,1,'IHP',NULL,NULL,1,578,NULL,NULL,NULL,13863)

	update FwkDomainOrganization set DepartmentId = @@IDENTITY where Id= @@IDENTITY

	insert into Organization_Signoff_Map 
	values (@@IDENTITY, 30109, 0, 9320)

	INSERT INTO FwkDomainOrganization 
	VALUES('Clinical Investigation','Clinical Investigation', 578, NULL, 1700, 'Partners > IHP > Clinical Investigation',3,1,'IHP',NULL,NULL,1,578,NULL,NULL,NULL,13863)

	update FwkDomainOrganization set DepartmentId = @@IDENTITY where Id= @@IDENTITY

	insert into Organization_Signoff_Map 
	values (@@IDENTITY, 47143, 0, 9320)

	INSERT INTO FwkDomainOrganization 
	VALUES('Imaging','Imaging', 578, NULL, 1700, 'Partners > IHP > Imaging',3,1,'IHP',NULL,NULL,1,578,NULL,NULL,NULL,13863)

	update FwkDomainOrganization set DepartmentId = @@IDENTITY where Id= @@IDENTITY

	insert into Organization_Signoff_Map 
	values (@@IDENTITY, 38106, 0, 9320)

	update Organization_WorkflowRole_Person_Map set PersonId = 6070 where OrganizationId = 240 --sutherland srh

	insert into Organization_WorkflowRole_Person_Map  --sutherland IHP
	values ('IRBChair',6070, 578, NULL)

	insert into Organization_WorkflowRole_Person_Map --sutherland IHP
	values ('HROAdminPerson',6070, 578, NULL)

	insert into Organization_WorkflowRole_Person_Map --sutherland srh
	values ('HROAdminPerson',6070, 240, NULL)

	insert into Organization_WorkflowRole_Person_Map --sutherland srh
	values ('HROIntake',6070, 240, 'Default')

	insert into Organization_WorkflowRole_Person_Map --sutherland IHP
	values ('HROIntake',6070, 578, 'Default')

	insert into Organization_WorkflowRole_Person_Map --sutherland srh
	values ('HROIntake',6070, 240, 'NINT_IR')

	insert into Organization_WorkflowRole_Person_Map --sutherland SRH
	values ('HROIntake',6070, 240, 'INT_IR')

	insert into Organization_WorkflowRole_Person_Map --sutherland SRH
	values ('HROIntake',6070, 240, 'CR')

	insert into Organization_WorkflowRole_Person_Map --sutherland IHP
	values ('HROIntake',6070, 578, 'INT_IR')

	insert into Organization_WorkflowRole_Person_Map --sutherland IHP
	values ('HROIntake',6070, 578, 'CR')

	insert into Organization_WorkflowRole_Person_Map --sutherland IHP
	values ('HROIntake',6070, 578, 'NINT_IR')

	update FwkDomainOrganization
	set ProtocolAdministratorId = 6070
	where FullyQualifiedname like '%SRH%'
	
	update FwkDomainOrganization
	set ProtocolAdministratorId = 6070
	where FullyQualifiedname like '%IHP%'


		BEGIN			
			SET @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
			UPDATE Version
			SET VersionName = @dbversion
			WHERE Description = 'Insight'

			PRINT 'Insight database version now ' + @dbversion
		END
	END

END -- End 2.3.0.013
-------------------------------------------------------------------------------
-- 2.3.0.014
-------------------------------------------------------------------------------
BEGIN
	IF ( [dbo].[GetNumericDatabaseVersion]() < 230014 )
	BEGIN		
		
		--kdh3 Correction to Trackit #8792 Pro Fees changes

		if not exists(select 1 from INFORMATION_SCHEMA.columns 
						where table_name = 'Chargemaster' and column_name = 'Id') begin
			exec('alter table Chargemaster add Id int identity')
			exec('alter table Chargemaster add CONSTRAINT [PK_Chargemaster] PRIMARY KEY CLUSTERED 
						([ID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]')
		end

		BEGIN			
			SET @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
			UPDATE Version
			SET VersionName = @dbversion
			WHERE Description = 'Insight'

			PRINT 'Insight database version now ' + @dbversion
		END
	END

END -- End 2.3.0.014
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- 2.3.0.015
-------------------------------------------------------------------------------
BEGIN
	IF ( [dbo].[GetNumericDatabaseVersion]() < 230015 )
	BEGIN		

		-- CJR20 - TrackIt 9940.  Need to clear some COIReports.
		BEGIN
			-- If a COI has N for WrittenAgreement, and no CSRL answer, delete the document and set the document id to 0
			DELETE Document 
				FROM answer a
				INNER JOIN questionnaireanswer qa on qa.id = a.questionnaireanswerid
				INNER JOIN coi_questionnaireanswer_map map on map.questionnaireanswerid = qa.id
				INNER JOIN conflictofinterest coi on coi.id = map.coiid and coi.reportingperiodid=2
					and coi.status in ('COISTATUS_SUBMITTED','COISTATUS_REVIEWED')
				INNER JOIN Document d on d.id = coi.documentid
				WHERE a.name = 'WrittenAgreement'
					AND a.value='N'
					AND not exists (select null from answer a2 where a2.questionnaireanswerid = qa.id
					AND a2.name = 'CSRL')
			
			UPDATE conflictofinterest SET documentid = 0 
				FROM answer a
				INNER JOIN questionnaireanswer qa on qa.id = a.questionnaireanswerid
				INNER JOIN coi_questionnaireanswer_map map on map.questionnaireanswerid = qa.id
				INNER JOIN conflictofinterest coi on coi.id = map.coiid and coi.reportingperiodid=2
					and coi.status in ('COISTATUS_SUBMITTED','COISTATUS_REVIEWED')
				WHERE a.name = 'WrittenAgreement'
					AND a.value='N'
					AND not exists (select null from answer a2 where a2.questionnaireanswerid = qa.id
					AND a2.name = 'CSRL')

			-- Highlight any row where there is no CSRL
			UPDATE QuestionnaireAnswer SET Highlight = 1 
				FROM answer a
				INNER JOIN questionnaireanswer qa on qa.id = a.questionnaireanswerid
				INNER JOIN coi_questionnaireanswer_map map on map.questionnaireanswerid = qa.id
				INNER JOIN conflictofinterest coi on coi.id = map.coiid and coi.reportingperiodid=2
					and coi.status in ('COISTATUS_SUBMITTED','COISTATUS_REVIEWED')
				LEFT JOIN answer a2 on a2.questionnaireanswerid = qa.id and a2.name = 'CSRL'
				WHERE a.name = 'WrittenAgreement' AND a.value='N' 
					OR (a.value = 'Y' AND ISNULL(a2.value, 'N') = 'N')
		END

		BEGIN			
			SET @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
			UPDATE Version
			SET VersionName = @dbversion
			WHERE Description = 'Insight'

			PRINT 'Insight database version now ' + @dbversion
		END
	END

END -- End 2.3.0.014
-------------------------------------------------------------------------------

